package com.game.server.db.dao;

import com.game.majiang.config.LogManager;
import com.game.server.db.Parameter;
import com.game.server.db.ParameterType;
import com.game.server.db.SqlHelper;
import com.game.server.db.entity.XyUnionChangesQuota;
import net.sf.json.JSONArray;
import net.sf.json.JSONObject;

import javax.sql.rowset.CachedRowSet;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.sql.Types;
import java.text.SimpleDateFormat;
import java.util.Date;

public class XyUnionChangesQuotaDao {

    //添加[俱乐部的用户额度变动]记录
    public static void insertInto(XyUnionChangesQuota quota) {
        //加入创建时间
        Date date = new Date();
        SimpleDateFormat formatter = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
        String date1=formatter.format(date);
        quota.setCreatedate(Timestamp.valueOf(date1));

        String sql = "INSERT INTO xy_union_changes_quota(unid,cid,me_uid,type,quota,residue,roomkey,createdate) VALUES (?,?,?,?,?,?,?,?);";
        Parameter p1 = new Parameter(Types.INTEGER, quota.getUnid(), ParameterType.Input);
        Parameter p2 = new Parameter(Types.INTEGER, quota.getCid(), ParameterType.Input);
        Parameter p3 = new Parameter(Types.INTEGER, quota.getMe_uid(), ParameterType.Input);
        Parameter p5 = new Parameter(Types.INTEGER, quota.getType(), ParameterType.Input);
        Parameter p6 = new Parameter(Types.INTEGER, quota.getQuota(), ParameterType.Input);
        Parameter p7 = new Parameter(Types.INTEGER, quota.getResidue(), ParameterType.Input);
        Parameter p8 = new Parameter(Types.BIGINT, quota.getRoomkey(), ParameterType.Input);
        Parameter p9 = new Parameter(Types.TIMESTAMP, quota.getCreatedate(), ParameterType.Input);
        SqlHelper.execute(sql, p1, p2, p3, p5, p6, p7, p8,p9);
    }

    public static JSONObject findPage(int limit, int page, int unid, int clubid, int type) {
        JSONObject jsonObject = new JSONObject();
        JSONArray array = new JSONArray();
        String where = "";
        if (type!=0){
            where = " and type="+type+" ";
        }
        String countSql = "select COUNT(t.roomkey) from (select * from \n" +
                "(select unid,cid,me_uid,type,quota,residue,createdate,roomkey from xy_union_changes_quota where type in(1,2) and unid="+unid+" and cid = "+clubid+where+") AS t1\n" +
                " UNION \n" +
                " (select unid,cid,me_uid,type,SUM(quota) as quota,residue,MAX(createdate) as createdate,roomkey from xy_union_changes_quota where type in(3,4,5) and unid="+unid+" and cid = "+clubid+where+" GROUP BY roomkey,type)) as t";
        String sql = "select * from \n" +
                "(select unid,cid,me_uid,type,quota,residue,createdate,roomkey from xy_union_changes_quota where type in(1,2) and unid="+unid+" and cid = "+clubid+where+") AS t1\n" +
                " UNION \n" +
                "(select t2.unid,t2.cid,t2.me_uid,t2.type,t2.quota,(select t3.residue from xy_union_changes_quota as t3 where t3.roomkey = t2.roomkey and t3.type = t2.type and t3.unid="+unid+" and t3.cid="+clubid+where+" ORDER BY t3.id desc LIMIT 1) as residue,t2.createdate,t2.roomkey from \n" +
                " (select unid,cid,me_uid,type,SUM(quota) as quota,MAX(createdate) as createdate,roomkey from xy_union_changes_quota where type in(3,4,5) and unid="+unid+" and cid = "+clubid+where+" GROUP BY roomkey,type) as t2) ORDER BY createdate desc LIMIT ?," + limit;
        Object scalar = SqlHelper.execScalar(countSql);
        int sumCount = Integer.parseInt(scalar.toString());
        int sumPage = (int) Math.ceil((double) sumCount / limit);
        page = page < 1 ? 1 : page > sumPage ? sumPage : page;
        page = page < 1 ? 1 : page;
        Parameter p1 = new Parameter(Types.INTEGER, ((page - 1) * limit), ParameterType.Input);
        CachedRowSet resultSet = SqlHelper.getResultSet(sql, p1);
        jsonObject.put("page", page);
        jsonObject.put("allpage", sumPage);
        jsonObject.put("allcount", sumCount);

        System.out.println("77777777777777777777777777777777777777777777777777777777777777777777777777");

        try {
            while (resultSet.next()) {
                LogManager.getInstance().Log("用户id为:"+resultSet);
                System.out.println("resultSet.getInt(1):"+resultSet.getInt(1));
                System.out.println("resultSet.getInt(2):"+resultSet.getInt(2));
                System.out.println("resultSet.getInt(3):"+resultSet.getInt(3));
                System.out.println("resultSet.getInt(4):"+resultSet.getInt(4));
                System.out.println("resultSet.getInt(5):"+resultSet.getInt(5));
                System.out.println("resultSet.getInt(6):"+resultSet.getInt(6));
                System.out.println("resultSet.getInt(7):"+resultSet.getTimestamp(7).getTime());
                System.out.println("resultSet.getInt(8):"+resultSet.getString(8));
                JSONObject jsonData = new JSONObject();
                jsonData.put("unid", resultSet.getInt(1));
                jsonData.put("cid", resultSet.getInt(2));
                jsonData.put("meUid", resultSet.getInt(3));
                jsonData.put("type", resultSet.getInt(4));
                jsonData.put("quota", resultSet.getInt(5));
                jsonData.put("residue", resultSet.getInt(6));
                jsonData.put("createdate", resultSet.getTimestamp(7).getTime());
                jsonData.put("roomkey", resultSet.getString(8));
                array.add(jsonData);
            }
        } catch (SQLException e) {
            LogManager.getInstance().errorLog(e);
        }
        jsonObject.put("array", array);
        return jsonObject;
    }
}
